package top.went.db.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import top.went.pojo.PaymentRecordsEntity;
import top.went.pojo.PlanPayDetailEntity;
import top.went.vo.PaymentRecordVO;

import java.util.Date;
import java.util.List;

/**
 * 付款记录Dao
 */
public interface PayRecordsDao extends JpaRepository<PaymentRecordsEntity,Integer> {
    /**
     * 查询所有付款计划
     * @return
     */
    public List<PaymentRecordsEntity> findAllByMagicDeleteOrderByPrIdDesc(Long delete,Pageable pageable);

    /**
     * 根据付款日期查询所有付款
     * @param pr_date
     * @return
     */
    @Query(nativeQuery = true,value ="SELECT * FROM tb_payment_records  WHERE  to_char(PR_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by ?#{#pageable}",
            countQuery = "SELECT count(*) FROM tb_payment_records  WHERE  to_char(PR_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 ")
    public Page<PaymentRecordsEntity> findAllByDate(String pr_date,Pageable pageable);

    /**
     * 查询所有付款数量
     * @return
     */
    @Query(nativeQuery = true,value = "select count(*) from tb_payment_records where magic_delete=0 order by pr_id desc")
    public int pr_findAllCount();

    /**
     * 根据付款日期查询所有付款数量
     * @param ppd_date
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT count(*) FROM tb_payment_records WHERE  to_char(PR_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by PR_ID desc")
    public int pr_findByPpdDateCount(String ppd_date);

    /**
     * 加载
     * @param delete
     * @param prId
     * @return
     */
    public PaymentRecordsEntity findAllByMagicDeleteAndPrId(long delete,int prId);
    /**
     * 根据采购单统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select max(nvl(p.PUR_THEME,'其他')) TYPE, sum(nvl(o.PR_MONEY,0)) count from TB_PAYMENT_RECORDS o left join TB_PURCHASE p on o.PUR_ID=p.PUR_ID group by o.PUR_ID")
    List<Object[]> prstatisticsByPur();
    /**
     * 根据年统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PR_DATE,'yyyy'),'其他') TYPE, sum(nvl(o.PR_MONEY,0)) count from TB_PAYMENT_RECORDS o group by to_char(o.PR_DATE,'yyyy')")
    List<Object[]> prstatisticsByYear();
    /**
     * 根据月统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PR_DATE,'yyyy-MM'),'其他') TYPE, sum(nvl(o.PR_MONEY,0)) count from TB_PAYMENT_RECORDS o group by to_char(o.PR_DATE,'yyyy-MM')")
    List<Object[]> prstatisticsByMonth();
    /**
     * 根据日期统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "   select nvl(to_char(o.PR_DATE,'yyyy-MM-dd'),'其他') TYPE, sum(nvl(o.PR_MONEY,0)) count from TB_PAYMENT_RECORDS o group by to_char(o.PR_DATE,'yyyy-MM-dd')")
    List<Object[]> prstatisticsByDate();

    /**
     * 根据客户统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "   select max(nvl(p.CUS_NAME,'其他')) TYPE, sum(nvl(o.PR_MONEY,0)) count from TB_PAYMENT_RECORDS o left join TB_CUSTOMER p on o.CUS_ID=p.CUS_ID group by o.CUS_ID")
    List<Object[]> prstatisticsByCus();

}
